package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.print.DocFlavor.STRING;

import org.apache.commons.lang3.StringUtils;

import dbutil.DBHelper;
import entity.Department;
import entity.DeptLicense;

public class DepartmentDao {
	
	
	// 保存一个部门
	public boolean saveOneDeparment(Department department) throws SQLException {
		Connection conn = null;
		String sql = "insert into departments (deptName, createDate) values(?, ?);";
		PreparedStatement pstmt = null;

		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, department.getDeptName());
			pstmt.setDate(2, new java.sql.Date(department.getCreateDate().getTime()));
			int i = pstmt.executeUpdate();
			
			if (i > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			pstmt.close();
		}
		
	}
	
	// 删除一个部门 by name
	public boolean delOneDepartment(String deptName) throws SQLException {
		Connection conn = null;
		String sql ="delete from departments where deptName=?";
		PreparedStatement pstmt = null;		
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, deptName);
			int i = pstmt.executeUpdate();
			
			if (i > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			pstmt.close();
		}
	}
	
	// 编辑部门
	public boolean updateDepartment(String oldName, String newName) throws SQLException {
		Connection conn = null;
		String sql ="update departments set deptName = ? where deptName = ?";
		PreparedStatement pstmt = null;		
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, newName);
			pstmt.setString(2, oldName);
			int i = pstmt.executeUpdate();
			
			if (i > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			pstmt.close();
		}
		
	}
	
	// 新增部门许可
	public boolean addDeptLicense(DeptLicense deptLicense) throws SQLException {
		StringBuffer sql = new StringBuffer("insert into deptLicense (deptName,softName,licenseNum,assigned,activated)");
		sql.append(" values(?,?,?,?,?)");
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			
			pstmt.setString(1, deptLicense.getDeptName());
			pstmt.setString(2, deptLicense.getSoftName());
			pstmt.setInt(3, deptLicense.getLicenseNum());
			pstmt.setInt(4, deptLicense.getAssigned());
			pstmt.setInt(5, deptLicense.getActivated());
			
			int i = pstmt.executeUpdate();
			
			if(i > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			pstmt.close();
		}
	}
	
	// 查询部门许可表
	public List<DeptLicense> queryDeptLicense(String deptName, String softName) throws SQLException {
		StringBuffer sql = new StringBuffer("select * from deptLicense where 1=1");
		if(!StringUtils.isEmpty(deptName)) {
			sql.append(" and deptName ='").append(deptName).append("'");
		}
		if(!StringUtils.isEmpty(softName)) {
			sql.append(" and softName ='").append(softName).append("'");
		}
		List<DeptLicense> list = null;
		DeptLicense deptLicense = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			list = new ArrayList<DeptLicense>();
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				deptLicense = new DeptLicense();
				deptLicense.setActivated(rs.getInt("activated"));
				deptLicense.setAssigned(rs.getInt("assigned"));
				deptLicense.setDeptName(rs.getString("deptName"));
				deptLicense.setLicenseNum(rs.getInt("licenseNum"));
				deptLicense.setSoftName(rs.getString("softName"));
				
				list.add(deptLicense);
			}
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		} finally {
			rs.close();
			pstmt.close();
		}
	}
	
	// 修改部门许可
	public boolean editLicenseNum(String deptName, String softName, int licenseNum) throws SQLException {
		String sql = "update deptLicense set licenseNum=? where deptName=? and softName=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBHelper.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, licenseNum);
			pstmt.setString(2, deptName);
			pstmt.setString(3, softName);
			
			int i = pstmt.executeUpdate();
			
			if(i > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			pstmt.close();
		}
	}
	
	// 查询一个部门
	public List<Department> queryDept(String deptName) throws SQLException {
		StringBuffer sql = new StringBuffer("select * from departments where 1=1");
		if(!StringUtils.isEmpty(deptName)) {
			sql.append(" and deptName like '%").append(deptName).append("%'");
		}
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Department department = null;
		List<Department> list = null;
		
		try {
			conn = DBHelper.getConnection();
			list = new ArrayList<Department>();
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				department = new Department();
				department.setCreateDate(rs.getDate("createDate"));
				department.setDeptName(rs.getString("deptName"));
				
				list.add(department);
			}
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		} finally {
			rs.close();
			pstmt.close();
		}
	}
	
	public DepartmentDao() {
	}

}
